postgres 主从

1 主库

1
docker run --name pgsmaster -p 5500:5432 -e POSTGRES_PASSWORD=pgsmaster -v $(pwd)/pgsmaster:/var/lib/postgresql/data -d postgres:9.5

从

1
docker run --name pgsslave  -p 5501:5432 -e POSTGRES_PASSWORD=pgsslave  -v $(pwd)/pgsslave:/var/lib/postgresql/data -d postgres:9.5

容器IP ：主：172.18.0.2
从：172.18.0.3

  1. 配置master（主库）
  2. 1 编辑pg_hba.conf，在最下面添加如下：
1
2
3
host    replication      replica     172.18.0.3/32          md5 
或者免密
host    replication      replica     172.18.0.3/32         trust

2.2 进入容器，登录PostgreSQL，创建复制账号并验证：

1
2
3
4
5
6
# 1.进入容器
     docker exec -it pgsmaster bash
# 2.连接PostgreSQL
     psql -U postgres
# 3.创建用户
CREATE ROLE replica login replication encrypted password 'replica';
1
2
3
4
5
6
7
8
9
10
11
12
13
2.3配置postgresql.conf
   
   listen_addresses = '*' 
   port = 5432 
   max_wal_senders = 2 
   wal_level = hot_standby
   archive_mode = on 
   archive_command = 'cd ./'
   hot_standby = on
   wal_keep_segments = 64
   wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
   max_connections = 100
   synchronous_standby_names = ''

2.4配置从库信息recovery.done

1
2
3
4
5
#编辑内容如下
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.18.0.3 port=5432 user=replica password=replica'
trigger_file = '/var/lib/postgresql/data/trigger_file'

docker restart pgsmaster

  1. 配置slave（从库）
1
2
3
4
5
3.1 导入主库数据
docker exec -it pgsslave /bin/bash
su postgres # 切换 postgres 用户
rm -rf /var/lib/postgresql/data/* # 清除从库数据
pg_basebackup -h 172.18.0.2 -U replica -D /var/lib/postgresql/data -X stream -P

3.2 编辑pg_hba.conf，在最下面添加如下：

1
2
3
host    replication      replica     172.18.0.2/32          md5 
或者免密
host    replication      replica     172.18.0.2/32         trust

3.3 添加 recovery.conf 文件

1
2
3
4
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.18.0.2 port=5432 user=replica password=replica'
trigger_file = '/var/lib/postgresql/data/trigger_file'

docker restart pgssalve

4 连接测试

1
2
3
4
// 进入主库容器
docker exec -it pgsmaster bash
// 查看复制状态
psql -U postgres -x -c "select * from pg_stat_replication;"

流复制数据同步测试

分别启动master，slave数据库
在master上创建一个数据库和临时表

1
2
3
4
5
6
7
8
9
10
psql -U postgres

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table tt(id serial not null,name text);
CREATE TABLE
test=# insert into tt(name) values ('china');
INSERT 0 1

在slave上查询刚才创建的表和数据，判定是否有数据同步

1
2
3
4
5
6
7
8
9
10
11
[postgres@bogon data]$ psql
psql (9.6.1)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from tt;
 id | name  
----+-------
  1 | china
(1 row)

5 主备切换

1
2
3
4
5
停掉主pgsmaster


在备机上执行
 touch /var/lib/postgresql/data/trigger_file;

postgresql 主备及切换-恢复方案

https://www.jianshu.com/p/12bc931ebba3

https://www.jianshu.com/p/343c3c8047f0

https://yq.aliyun.com/articles/641773

https://www.cnblogs.com/yjf512/p/4499547.html